package mysql

import (
	"database/sql"
	"fmt"
	"hello/dao"
	"hello/jwt"
	"strconv"
	_ "time"
	time2 "time"

	_ "github.com/go-sql-driver/mysql"
	gormMysql "gorm.io/driver/mysql"
	gorm "gorm.io/gorm"
)

func InitDB() (err error) {
	dsn := "zhaoxin:yu020506@tcp(1.117.163.52:3306)/zhaoxin?charset=utf8mb4&parseTime=True&loc=Asia%2FShanghai"
	dao.DB, err = sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	dao.GormDB, err = gorm.Open(gormMysql.New(gormMysql.Config{
		Conn: dao.DB,
	}), &gorm.Config{})
	if err != nil {
		return err
	}
	err = dao.DB.Ping()
	if err != nil {
		return err
	}
	return nil
}
// InsertUser
// @Router /user [GET]
// @Summary 报名（添加新用户）
// @Description 可通过此接口报名（添加新用户）
// @Tags 用户相关接口
// @Accept application/json
// @Param name query string true "用户名"
// @Param number query string true "学号"
// @Param gender query string true "性别"
// @Param class query string true "组别"
// @Param college query string true "学院"
// @Param major query string true "专业"
// @Param phone query string true "电话"
// @Param qq query string true "qq"
// @Produce application/json
// @Security ApiKeyAuth
// @Success 500 {object} dao.SwaggerUserSuccess {"code": 500, "msg": "用户信息已上传，邮件发送已成功发送", "user": { "id": "15", "name": "喻鑫锐5", "number": "20201126178", "gender": "男", "class": "开发组", "college": "计算机与信息学院", "major": "计算机科学与技术", "phone": "17786170105", "qq": "54535282", "score": "", "status": "" }}
// @Failure 304 {object} dao.SwaggerUserFailed {"code": 304,"msg": "用户信息不完整，请确认用户信息是否完整！"}
func InsertUser(name string, number string, gender string, class string, college string, major string, phone string, qq string) (int, string, dao.User, string) {
	sqlStr := "insert into users(name,number,gender,class,college,major,phone,qq) values (?,?,?,?,?,?,?,?)"
	queryStr := "select count(*) from users where name = ? and number = ?"
	var User dao.User
	if name == "" || number == "" || gender == "" || class == "" || college == "" || major == "" || phone == "" || qq == "" {
		return 304, "用户信息输入有误，请确认是否有信息漏填！", User, ""
	}
	num := 0
	rows, err := dao.DB.Query(queryStr, name, number)
	if err != nil {
		return 300, err.Error(), User, ""
	}
	for rows.Next() {
		err := rows.Scan(&num)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return 301, err.Error(), User, ""
		}
	}
	if num != 0 { //向数据库中提交数据的时候应该先判断当前数据库中有没有这一条数据，如果没有的话才会插入数据，有的话就提示已经注册过
		return 303, "该用户已注册过！", User, ""
	}

	ret, err := dao.DB.Exec(sqlStr, name, number, gender, class, college, major, phone, qq)
	if err != nil {
		return 400, err.Error(), User, ""
	}
	_, err = ret.RowsAffected()
	if err != nil {
		return 401, err.Error(), User, ""
	} else {
		id, _ := ret.LastInsertId()
		User.ID = strconv.FormatInt(id, 10)
		User.Name = name
		User.Number = number
		User.Gender = gender
		User.Class = class
		User.College = college
		User.Major = major
		User.Phone = phone
		User.QQ = qq
		token, _ := jwt.GenToken(User.ID, User.Name, User.Number)
		return 500, "success", User, token
	}
}

func GetUserByName(name string) (int,string,[]dao.User){
	sqlStr := "select * from users where name like ?"
	var users []dao.User
	var id string
	var name2 string
	var number string
	var gender string
	var class string
	var college string
	var major string
	var phone string
	var qq string
	var score string
	var status string
	rows,err := dao.DB.Query(sqlStr,"%"+name+"%")
	if err != nil{
		return 401,err.Error(),users
	}
	for rows.Next(){
		err := rows.Scan(&id,&name2,&number,&gender,&class,&college,&major,&phone,&qq,&score,&status)
		if err != nil{
			return 402,err.Error(),users
		}
		users = append(users,dao.User{
			ID:      id,
			Name:    name2,
			Number:  number,
			Gender:  gender,
			Class:   class,
			College: college,
			Major:   major,
			Phone:   phone,
			QQ:      qq,
			Score:   score,
			Status:  status,
		})
	}
	return 500,"",users
}

// GetImgList @Router /img [GET]
// @Summary 获取所有的图片
// @Description 可通过此接口获取所有的图片资源
// @Tags 资源接口
// @Accept application/json
// @Produce application/json
// @Security ApiKeyAuth
// @Success 201 {object} string
func GetImgList() (int, string, []string) {
	sqlStr := "select * from img"
	rows, err := dao.DB.Query(sqlStr)
	str := ""
	var arr []string
	if err != nil {
		return 400, err.Error(), arr
	}
	for rows.Next() {
		err = rows.Scan(&str)
		if err != nil {
			return 401, err.Error(), arr
		}
		arr = append(arr, str)
	}
	return 500, "success", arr
}

func GetAllTeamers() (int, string, []dao.AllUser) {
	sqlStr := "select * from people"
	var arr []dao.AllUser
	var dataUser dao.AllUser
	rows, err := dao.DB.Query(sqlStr)
	if err != nil {
		return 401, err.Error(), arr
	}
	for rows.Next() {
		err := rows.Scan(&dataUser.Number, &dataUser.Name, &dataUser.Class, &dataUser.Major, &dataUser.Blog, &dataUser.Grade, &dataUser.Flag)
		if err != nil {
			return 402, err.Error(), arr
		}
		arr = append(arr, dataUser)
	}
	return 500, "", arr
}

func GetTeamClass(id string) (int, string, []dao.AllUser) {
	sqlStr := "select * from people where flag = ?"
	var arr []dao.AllUser
	var dataUser dao.AllUser
	rows, err := dao.DB.Query(sqlStr, id)
	if err != nil {
		return 401, err.Error(), arr
	}
	for rows.Next() {
		err := rows.Scan(&dataUser.Number, &dataUser.Name, &dataUser.Class, &dataUser.Major, &dataUser.Blog, &dataUser.Grade, &dataUser.Flag)
		if err != nil {
			return 402, err.Error(), arr
		}
		arr = append(arr, dataUser)
	}
	return 500, "", arr
}

func GetUser(id string) (int, string, dao.User) {
	sqlStr := "select id, name, number, gender, class, college, major, phone, qq from users where id = ?"
	var user dao.User
	err := dao.DB.QueryRow(sqlStr, id).Scan(&user.ID, &user.Name, &user.Number, &user.Gender, &user.Class, &user.College, &user.Major, &user.Phone, &user.QQ)
	if err != nil {
		return 401, err.Error(), user
	}
	fmt.Println(user)
	return 500, "", user
}

func GetAllUser() (int, string, []dao.User, int64) {
	//var user dao.User
	var users []dao.User

	result := dao.GormDB.Find(&users)
	if result.Error != nil {
		return 401, result.Error.Error(), users, 0
	} else {
		return 500, "", users, result.RowsAffected
	}
}

func ModifyUser(user dao.User) (int, string, dao.User) {
	var user2 dao.User
	result := dao.GormDB.Table("users").Where("id = ?", user.ID).First(&user2)
	if result.Error != nil {
		return 401, result.Error.Error(), user2
	}
	id := user2.ID
	result = dao.GormDB.Table("users").Where("id = ?", id).Updates(user)
	if result.Error != nil {
		return 402, result.Error.Error(), user2
	} else {
		dao.GormDB.Where("id = ?", id).First(&user2)
		return 500, "success", user2
	}
}

func DeleteUser(user dao.User) (int, string, dao.User) {
	var userData dao.User
	//fmt.Println("success")
	result := dao.GormDB.Where("id = ?", user.ID).First(&userData)
	fmt.Println(result)
	if result.Error != nil {
		return 404, result.Error.Error(), userData
	}
	sqlStr := "delete from users where id = ?"
	ret, err := dao.DB.Exec(sqlStr, user.ID)
	if err != nil {
		return 401, err.Error(), userData
	}
	_, err2 := ret.RowsAffected()
	if err2 != nil {
		return 402, err2.Error(), userData
	} else {
		return 500, "", userData
	}

}

func GetAllComments() (int, string, []dao.Comment2) {
	var commentArray []dao.Comment2
	sqlStr := "select * from comments"
	id := ""
	username := ""
	number := ""
	content := ""
	time := ""
	var timeNow time2.Time
	rows, err := dao.DB.Query(sqlStr)
	if err != nil {
		return 401, err.Error(), []dao.Comment2(nil)
	}
	for rows.Next() {
		err2 := rows.Scan(&id, &username, &number, &content, &timeNow)

		time = timeNow.Format("2006-01-02 15:04:05")
		if err2 != nil {
			return 402, err2.Error(), []dao.Comment2(nil)
		} else {
			commentArray = append(commentArray, dao.Comment2{
				ID:       id,
				Username: username,
				Number:   number,
				Content:  content,
				Time:     time,
			})
		}
	}
	return 500, "", commentArray
}

func GetCommentByName(name string) (int,string,[]dao.Comment2){
	sqlStr := "select * from comments where username like ?"
	var arr []dao.Comment2
	var id string
	var username string
	var number string
	var content string
	var timeString string
	var time time2.Time
	rows,err := dao.DB.Query(sqlStr,"%"+name+"%")
	if err != nil{
		return 401,err.Error(),arr
	}
	for rows.Next(){
		err = rows.Scan(&id,&username,&number,&content,&time)
		timeString = time.Format("2006-01-02 15:04:05")
		if err != nil{
			return 402,err.Error(),arr
		}
		arr = append(arr,dao.Comment2{
			ID:       id,
			Username: username,
			Number:   number,
			Content:  content,
			Time:    timeString,
		})
	}
	return 500,"",arr
}

func InsertComment(comment dao.Comment) (int, string, int64, dao.Comment) {
	sqlStr := "insert into comments(username,number,content,time) values (?,?,?,?)"
	ret, err := dao.DB.Exec(sqlStr, comment.Username, comment.Number, comment.Content, comment.Time)
	if err != nil {
		return 401, err.Error(), -1, dao.Comment{}
	}
	id, err2 := ret.LastInsertId()
	if err2 != nil {
		return 402, err.Error(), -1, dao.Comment{}
	} else {
		comment.ID = strconv.FormatInt(id, 10)
		return 500, "", id, comment
	}
}

func DeleteComment(comment dao.Comment) (int, string, dao.Comment) {
	var commentData dao.Comment
	sqlStr := "select * from comments where id = ?"
	err3 := dao.DB.QueryRow(sqlStr, comment.ID).Scan(&commentData.ID, &commentData.Username, &commentData.Number, &commentData.Content, &commentData.Time)
	if err3 != nil {
		return 401, err3.Error(), dao.Comment{}
	}
	sqlStr = "delete from comments where id = ?"
	ret, err := dao.DB.Exec(sqlStr, comment.ID)
	if err != nil {
		return 401, err.Error(), dao.Comment{}
	}
	n, err2 := ret.RowsAffected()
	if err2 != nil {
		return 402, err.Error(), dao.Comment{}
	}
	if n == 0 {
		return 402, "数据库中不存在该评论", dao.Comment{}
	}
	return 500, "", commentData

}

func UpdateComment(comment dao.Comment) (int, string, dao.Comment) {
	fmt.Println(comment)
	result := dao.GormDB.Table("comments").Where("id = ?", comment.ID).Updates(comment) // 为什么只能更新 users 这张表的数据   怎么样 gormDB 此时修改对应的表
	if result.Error != nil {
		return 401, result.Error.Error(), comment
	} else {
		result = dao.GormDB.Table("comments").Where("id = ?", comment.ID).First(&comment) // 返回更新之后的评论数据
		if result.Error != nil {
			return 402, result.Error.Error(), comment
		}
		return 500, "", comment
	}
}

func GetSingleComment(id string) (int, string, dao.Comment) {
	sqlStr := "select * from comments where id = ?"
	var commentData dao.Comment
	err := dao.DB.QueryRow(sqlStr, id).Scan(&commentData.ID, &commentData.Username, &commentData.Number, &commentData.Content, &commentData.Time)
	if err != nil {
		return 401, err.Error(), dao.Comment{}
	}
	return 500, "success", commentData
}


func GetAllProcesses() (int,string,[]dao.Process){
	sqlStr := "select * from process"
	var processArray []dao.Process
	rows,err := dao.DB.Query(sqlStr)
	num := ""
	title := ""
	content := ""
	focus := ""
	if err != nil{
		return 401,err.Error(),processArray
	}
	for rows.Next(){
		err := rows.Scan(&num,&title,&content,&focus)
		if err != nil{
			return 402,err.Error(),processArray
		}
		processArray = append(processArray,dao.Process{
			Num:     num,
			Title:   title,
			Content: content,
			Focus: focus,
		})
	}
	return 500,"",processArray
}

func GetSingleProcess(id string) (int,string,dao.Process){
	sqlStr := "select * from process where num = ?"
	var process dao.Process
	err := dao.DB.QueryRow(sqlStr,id).Scan(&process.Num,&process.Title,&process.Content,&process.Focus)
	if err != nil{
		return 401,err.Error(),process
	}else{
		return 500,"",process
	}
}

func UpdateProcess(process dao.Process) (int,string,dao.Process){
	result := dao.GormDB.Table("process").Where("num = ?",process.Num).Updates(process)
	if result.Error != nil{
		return 401,result.Error.Error(),dao.Process{}
	} else{
		var data dao.Process
		sqlStr := "select * from process where num = ?"
		err := dao.DB.QueryRow(sqlStr,process.Num).Scan(&data.Num,&data.Title,&data.Content)
		if err != nil{
			return 401,err.Error(),data
		}else{
			return 500,"",data
		}
	}
}


func GetAllShowData() (int,string,[]dao.ShowData){
	sqlStr := "select * from showData"
	var id int
	var content string
	var Type string
	var Img string
	var showDataArray []dao.ShowData
	rows,err := dao.DB.Query(sqlStr)
	if err != nil{
		return 401,err.Error(),showDataArray
	}
	for rows.Next(){
		err = rows.Scan(&id,&content,&Img,&Type)
		if err != nil{
			return 402,err.Error(),showDataArray
		}
		showDataArray = append(showDataArray,dao.ShowData{
			ID:      id,
			Content: content,
			Type:    Type,
			Img:     Img,
		})
	}
	return 500,"",showDataArray
}

func GetClassShowData(types string) (int,string,[]dao.ShowData){
	sqlStr := "select * from showData where type = ?"
	var id int
	var content string
	var Type string
	var Img string
	var showDataArray []dao.ShowData
	rows,err := dao.DB.Query(sqlStr,types)
	if err != nil{
		return 401,err.Error(),showDataArray
	}
	for rows.Next(){
		err = rows.Scan(&id,&content,&Img,&Type)
		if err != nil{
			return 402,err.Error(),showDataArray
		}
		showDataArray = append(showDataArray,dao.ShowData{
			ID:      id,
			Content: content,
			Type:    Type,
			Img:     Img,
		})
	}
	return 500,"",showDataArray
}

func GetShowDataClassSum() (int,string,dao.ShowDataMade){
	sqlStr := "select count(*) from showData where type = ?"
	var sum int
	var data dao.ShowDataMade
	err := dao.DB.QueryRow(sqlStr,"yj").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.Yj = sum

	err = dao.DB.QueryRow(sqlStr,"yx").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.Yx = sum

	err = dao.DB.QueryRow(sqlStr,"web").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.Web = sum
	return 500,"",data
}

func CheckYuQAdmin(qq string) (int,string,int){  // 判断是否是管理员
	sqlStr := "select count(*) from YuQAdmin where qq = ?"
	row := 0  // 共有多少条数据
	err := dao.DB.QueryRow(sqlStr,qq).Scan(&row)
	if err != nil{
		return 401,err.Error(),0
	} else{
		if row != 0{
			return 402,"该qq已是管理员",row
		}else{
			return 500,"该用户不是管理员",row  // 不是管理员才可以进行插入
		}
	}
}

func AddYuqAdmin(qq string) (int,string,int64){
	sqlStr := "insert into YuQAdmin(qq) values(?)"
	//var object dao.YuQAdmin
	ret, err := dao.DB.Exec(sqlStr,qq)
	if err != nil{
		return 401,err.Error(),0
	}
	n,err := ret.LastInsertId()
	if err != nil{
		return 402,err.Error(),0
	}else{
		return 500,"",n
	}
}

func GetAllYuqAdmin() (int,string,[]dao.YuQAdmin){
	sqlStr := "select * from YuQAdmin"
	var arr []dao.YuQAdmin
	var ID int
	var qq string
	rows,err := dao.DB.Query(sqlStr)
	if err != nil{
		return 401,err.Error(),arr
	}
	for rows.Next(){
		err := rows.Scan(&ID,&qq)
		if err != nil{
			return 402,err.Error(),arr
		}
		arr = append(arr,dao.YuQAdmin{
			ID: ID,
			QQ: qq,
		})
	}
	return 500,"",arr
}

func GetAllAward() (int,string,[]dao.AwardList){
	sqlStr := "select * from award"
	var arr []dao.AwardList
	var id int
	var name string
	var url string
	var timer string
	var team string
	var class string
	var members []string
	var temp string
	rows,err := dao.DB.Query(sqlStr)
	if err != nil{
		return 401,err.Error(),arr
	}
	for rows.Next(){
		err = rows.Scan(&id,&name,&url,&timer,&team,&class)
		if err != nil{
			return 402,err.Error(),arr
		}
		sqlStr = "select name from team where team = ?"
		data,err := dao.DB.Query(sqlStr,team)
		if err != nil{
			return 401,err.Error(),arr
		}
		for data.Next(){
			err = data.Scan(&temp)
			if err != nil{
				return 402,err.Error(),arr
			}
			members = append(members,temp)
		}
		arr = append(arr,dao.AwardList{
			ID:      id,
			Name:    name,
			Url:     url,
			Time:    timer,
			Team:    team,
			Class:   class,
			Members: members,
		})
	}
	return 500,"",arr
}

func GetUserSex() (int,string,dao.Gender){
	sqlStr := "select count(*) from users where gender = ?"
	var gender dao.Gender
	var sum int
	err := dao.DB.QueryRow(sqlStr,"男").Scan(&sum)
	if err != nil{
		return 401,err.Error(),gender
	}
	gender.Man = sum
	err = dao.DB.QueryRow(sqlStr,"女").Scan(&sum)
	if err != nil{
		return 401,err.Error(),gender
	}
	gender.Woman = sum
	return 500,"",gender
}
func GetGenderForClasses() (int,string,dao.GenderForClasses){
	sqlStr := "select count(*) from users where gender = ? and class = ?"
	var sum int
	var data dao.GenderForClasses
	err := dao.DB.QueryRow(sqlStr,"女","开发组").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.KaiFaWoman = sum


	err = dao.DB.QueryRow(sqlStr,"女","智能组").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.ZhiNengWoman = sum


	err = dao.DB.QueryRow(sqlStr,"男","开发组").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.KaiFaMan = sum


	err = dao.DB.QueryRow(sqlStr,"男","智能组").Scan(&sum)
	if err != nil{
		return 401,err.Error(),data
	}
	data.ZhiNengMan = sum
	return 500,"",data
}

func CheckMusicName(name string) (int,string,string){
	sqlStr := "select count(*) from music where name = ?"
	var sum int
	err := dao.DB.QueryRow(sqlStr,name).Scan(&sum)
	if err != nil{
		return 401,err.Error(),""
	}
	if sum == 0{
		return 404,"","无此歌曲信息"
	}else{
		return 500,"","有此信息"
	}

}